package week05.jdbc;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.Collections;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

/**
 * @author edward
 * @since 2021-07-24
 */
public class JdbcCRUD {
    private final DataSource dataSource;
    public JdbcCRUD(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    public static void main(String[] args) throws SQLException {
        DataSource ds = DataSourceFactory.getDataSource("sqlite");
        crud(ds);

        transactionCrud(ds);

        ds = DataSourceFactory.getDataSource("hikari");
        crud(ds);
    }

    private static void transactionCrud(DataSource ds) throws SQLException {
        JdbcCRUD jdbc = new JdbcCRUD(ds);
        jdbc.createTable();
        Connection conn = jdbc.getConnection();
        try {
            conn.setAutoCommit(false);
            jdbc.execUpdate(conn, "insert into t_test(id, col_1, col_2) values(?, ?, ?)", Arrays.asList("1", "col_val_1", "col_val_2"));
            jdbc.execUpdate(conn, "update t_test set col_1=? where id=?", Arrays.asList("col_val_upd_1", "1"));

            System.out.println(jdbc.doSelect(conn, "select * from t_test where id in (?)", Collections.singletonList("1")));

            jdbc.execUpdate(conn, "delete from t_test where id=?", Collections.singletonList("1"));
            conn.commit();
        } catch (Exception e) {
            conn.rollback();
        } finally {
            jdbc.dropTable();
            conn.setAutoCommit(true);
            conn.close();
        }
    }

    private static void crud(DataSource ds) throws SQLException {
        JdbcCRUD jdbc = new JdbcCRUD(ds);
        try {
            jdbc.createTable();
            jdbc.insert("insert into t_test(id, col_1, col_2) values(?, ?, ?)", Arrays.asList("1", "col_val_1", "col_val_2"));
            jdbc.update("update t_test set col_1=? where id=?", Arrays.asList("col_val_upd_1", "1"));
            jdbc.delete("delete from t_test where id=?", Collections.singletonList("1"));
            jdbc.batch("insert into t_test(id, col_1, col_2) values(?, ?, ?)", Arrays.asList(
                    Arrays.asList("2", "col1_val_2", "col2_val_2"),
                    Arrays.asList("3", "col1_val_3", "col2_val_3"),
                    Arrays.asList("4", "col1_val_4", "col2_val_4")
                    ));
            System.out.println(jdbc.select("select * from t_test where id in (?, ?, ?)", Arrays.asList("2", "3", "4")));
        } finally {
            jdbc.dropTable();
        }
    }

    public void createTable() throws SQLException {
        String sql = "create table t_test (" +
                "id varchar(60) NOT NULL, " +
                "col_1 varchar(60) NULL, " +
                "col_2 varchar(60) NULL, " +
                "primary key(id))";
        try (Connection conn = getConnection();
              PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.execute();
        }
    }
    public void dropTable() throws SQLException {
        try (Connection conn = getConnection();
             PreparedStatement ps = conn.prepareStatement("drop table t_test")) {
            ps.execute();
        }
    }
    public List<Map<String, Object>> doSelect(Connection conn, String sql, List<Object> params) throws SQLException {
        if (sql == null || !sql.toLowerCase().startsWith("select")) {
            throw new IllegalArgumentException("SQL must starts with 'select'");
        }
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            if (params != null) {
                for (int i = 0; i < params.size(); i++) {
                    ps.setObject(i+1, params.get(i));
                }
            }
            List<Map<String, Object>> result = new LinkedList<>();
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    Map<String, Object> item = new LinkedHashMap<>();
                    ResultSetMetaData metaData = rs.getMetaData();
                    for (int i = 1; i <= metaData.getColumnCount(); i++) {
                        String colName = metaData.getColumnLabel(i);
                        item.put(colName.toLowerCase(), rs.getObject(colName));
                    }
                    result.add(item);
                }
            }
            return result;
        }
    }
    public List<Map<String, Object>> select(String sql, List<Object> params) throws SQLException {
        try (Connection conn = getConnection()) {
            return doSelect(conn, sql, params);
        }
    }
    public int insert(String sql, List<Object> params) throws SQLException {
        if (sql == null || !sql.toLowerCase().startsWith("insert")) {
            throw new IllegalArgumentException("SQL must starts with 'insert'");
        }
        try (Connection conn = getConnection()) {
            return execUpdate(conn, sql, params);
        }
    }
    public int update(String sql, List<Object> params) throws SQLException {
        if (sql == null || !sql.toLowerCase().startsWith("update")) {
            throw new IllegalArgumentException("SQL must starts with 'update'");
        }
        try (Connection conn = getConnection()) {
            return execUpdate(conn, sql, params);
        }
    }
    public int delete(String sql, List<Object> params) throws SQLException {
        if (sql == null || !sql.toLowerCase().startsWith("delete")) {
            throw new IllegalArgumentException("SQL must starts with 'delete'");
        }
        try (Connection conn = getConnection()) {
            return execUpdate(conn, sql, params);
        }
    }
    public int execUpdate(Connection conn, String sql, List<Object> params) throws SQLException {
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            if (params != null) {
                for (int i = 0; i < params.size(); i++) {
                    ps.setObject(i + 1, params.get(i));
                }
            }
            return ps.executeUpdate();
        }
    }
    public void batch(String sql, List<List<Object>> paramsList) throws SQLException {
        try (Connection conn = getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            for (List<Object> params : paramsList) {
                for (int i = 0; i < params.size(); i++) {
                    ps.setObject(i + 1, params.get(i));
                }
                ps.addBatch();
            }
            ps.executeBatch();
        }
    }
    public Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }
}
